

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;

import java.sql.*;

public class DBConnect {
PreparedStatement pstmt;
ResultSet result;
Connection conn;
Statement stmt;

public Connection connect(){

    String user = "root";
    String pass = "root";
    String URL= "jdbc:mysql://localhost:3306/introse";

    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(URL, user, pass);
    }
    catch(Exception e) {
    System.out.println(e.getMessage());
    }
    return conn;

    } 
public void viewUsers(){
    try {
         conn = new DBConnect().connect();
         pstmt = conn.prepareStatement("SELECT * from user_accounts");
         
         pstmt.executeQuery();
         result = pstmt.getResultSet();

        }
        catch (Exception e){   
        }
}
  
public void viewInventory(){
    try {
         conn = new DBConnect().connect();
         pstmt = conn.prepareStatement("SELECT * from inventory");

         pstmt.executeQuery();
         result = pstmt.getResultSet();

        }
        catch (Exception e)
        {   e.printStackTrace(); }
}

public ResultSet getResult() {
        return result;
    }

    public void AddQuery(String query)
    {
        try {
            conn = new DBConnect().connect();
            stmt = conn.createStatement();

            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

public ArrayList view(String query, int i){
    ArrayList row = new ArrayList();
    ArrayList col = new ArrayList();
    try{
        if(i == 1){
            conn = new DBConnect().connect();
            pstmt = conn.prepareStatement("SELECT * from inventory");

            pstmt.executeQuery();
            result = pstmt.getResultSet();

                while(result.next()){
                col.add(result.getString("name"));
                col.add(result.getString("weight"));
                col.add(result.getString("price"));
                row.add(col);
                col = new ArrayList<String>();
                }
            }
        /*
        else if(i == 2)
            {
                conn = new DBConnect().connect();
                pstmt = conn.prepareStatement("SELECT * from employee");

                pstmt.executeQuery();
                result = pstmt.getResultSet();

                while(result.next()){
                    col.add(result.getString("employee"));
                    col.add(result.getString("position"));
                    col.add(result.getString("salary"));

                    row.add(col);
                    col = new ArrayList<String>();
                }
            }*/
        }
        catch(SQLException ex){
            ex.printStackTrace();
        }
        finally{
            try{
                if(result != null)
                    result.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    conn.close();
            }
       catch (SQLException ex){
         ex.printStackTrace();
       }
       return row;
    }
  }

public void close(){
    try{
        conn.close();
    }catch(Exception e){
        
    }
}

}